package com.estore.service.persitence;

import com.estore.service.model.ItemIssue;
import com.estore.service.model.ItemReturn;
import com.estore.service.model.ItemTransactionType;
import com.estore.service.persitence.core.MySQLDatabaseConnector;
import com.estore.service.util.exception.EStoreException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ItemTransactionDatabaseManager {

    private Connection conn;
    private ResultSet result;

    public boolean issueItem(ItemIssue itemIssue) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertItemIssue = null;
        try {
            insertItemIssue = conn.prepareStatement("insert into itemtransaction set"
                    + " itemid=?, transactiondate=STR_TO_DATE(?,'d/M/yyyy'), itemcondition=?, expecteddateofreturn=?,"
                    + " staffid=?, transactiontype=?");
            insertItemIssue.setInt(1, itemIssue.getItemId());
            insertItemIssue.setString(2, itemIssue.getDateIssued());
            insertItemIssue.setString(3, itemIssue.getItemCondition());
            insertItemIssue.setString(4, itemIssue.getExpectedDateOfReturn());
            insertItemIssue.setInt(5, itemIssue.getStaffId());
            insertItemIssue.setString(6, ItemTransactionType.ISSUE_ITEM);
            insertItemIssue.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public boolean returnItem(ItemReturn itemReturn) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertItemReturn = null;
        try {
            insertItemReturn = conn.prepareStatement("insert into itemtransaction set"
                    + " itemid=?, transactiondate=STR_TO_DATE(?,'d/M/yyyy'), itemcondition=? staffid=?, "
                    + "transactiontype=?, itemissueid=?");
            insertItemReturn.setInt(1, itemReturn.getItemId());
            insertItemReturn.setString(2, itemReturn.getDateReturned());
            insertItemReturn.setString(3, itemReturn.getItemCondition());
            insertItemReturn.setInt(4, itemReturn.getStaffId());
            insertItemReturn.setString(5, ItemTransactionType.RETURN_ITEM);
            insertItemReturn.setInt(6, itemReturn.getItemIssueId());
            insertItemReturn.executeUpdate();
        } catch (SQLException e) {           
            inserted = false;
            throw new EStoreException(e);
        }
        return inserted;
    }   

    public ItemIssue getItemIssued(ResultSet result) {
        ItemIssue itemIssue = new ItemIssue();
        try {
            itemIssue = new ItemIssue();
            itemIssue.setDateIssued(result.getString("name"));
            itemIssue.setExpectedDateOfReturn(result.getString("batchnumber"));
            itemIssue.setId(result.getInt("dateofpurchase"));
            itemIssue.setItemCondition(result.getString("depreciationrate"));
            itemIssue.setItemId(result.getInt("description"));
            itemIssue.setStaffId(result.getInt("serialnumber"));
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return itemIssue;
    }

    public List<ItemIssue> getAllItemsIssued() {
        List<ItemIssue> itemIssues = new ArrayList<ItemIssue>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllItemsIssued = null;
        try {
            selectAllItemsIssued = conn.prepareStatement("select * from itemtransaction where transactiontype=?");
            selectAllItemsIssued.setString(1, ItemTransactionType.ISSUE_ITEM);
            result = selectAllItemsIssued.executeQuery();
            while (result.next()) {
                itemIssues.add(getItemIssued(result));
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return itemIssues;
    }

    public int getAllItemCount() {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllItemCount = null;
        try {
            selectAllItemCount = conn.prepareStatement("select count(*) from item");
            result = selectAllItemCount.executeQuery();
            while (result.next()) {
                return result.getInt(1);
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return 0;

    }
}
